﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using Excel;
using System.Data;
using System.IO;
using System.Data.Common;
using OfficeOpenXml;
using System.Data.SqlClient;
using System.Net.Mail;
using System.ServiceModel.Activation;

namespace AdvancedStandingAndScholarship.Web
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "ASASService" in code, svc and config file together.
    public class ASASService : IASASService
    {
        public void DoWork(string filePath)
        {

        }

        # region SQL common methods

        //crash when using acs
        //public static string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
        //public static string conString = "Server=.\\LOCALHOST; Database=ASASdb; Integrated Security= true;";
        public static string conString = "Server=LOCALHOST; Database=ASASdb; Integrated Security= true;";
        //public static string conString = "Data Source=xwizp2ppu2.database.windows.net;Initial Catalog=ASASDB;Persist Security Info=True;User ID=asas@xwizp2ppu;Password=Iamhere0;Pooling=false";
        public SqlConnection conn = null;

        private SqlCommand SqlCommandConnection()
        {
            SqlCommand sql = new SqlCommand();
            sql.Connection = conn;
            return sql;
        }

        private void SqlOpenConnection()
        {
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = conString;
                conn.Open();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception)
            {
                SqlConnection.ClearAllPools();
            }
        }

        private void SqlCloseConnection()
        {
            conn.Close();
            conn.Dispose();
        }

        # endregion

        //change add method (file id )

        //delete method (remove all selected records)

        #region CRUD for school

        public List<KeyValuePair<string, string>> getAllSchoolWithCountry()
        {
            List<KeyValuePair<string, string>> schoolList = new List<KeyValuePair<string, string>>();

            try
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "Select * From university";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    schoolList.Add(new KeyValuePair<string, String>(dr["country"].ToString(), dr["uni_name"].ToString()));
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                SqlCloseConnection();
            }

            return schoolList;
        }

        public List<string> getAllSchool()
        {
            List<string> schoolList = new List<string>();

            try
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "Select uni_name From university";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    schoolList.Add(dr["uni_name"].ToString());
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                SqlCloseConnection();
            }

            return schoolList;
        }

        public List<string> getAllCountry()
        {
            List<string> countryList = new List<string>();

            try
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "Select DISTINCT country From university";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    countryList.Add(dr["country"].ToString());
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                SqlCloseConnection();
            }

            return countryList;
        }

        public List<string> getSelectedSchool(string country)
        {
            List<string> schoolList = new List<string>();

            try
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "SELECT uni_name FROm university WHERE country = @country";
                comm.Parameters.AddWithValue("@country", country);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    schoolList.Add(dr["uni_name"].ToString());
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                SqlCloseConnection();
            }

            return schoolList;
        }

        #endregion

        #region CRUD for student

        //check if student records exist by using checkExist(), if not, add student
        public List<Student> addNewStudent(List<Student> studentList, string email)
        {
            int fileID = 0;

            try
            {
                SqlOpenConnection();
                SqlCommand comm;

                comm = SqlCommandConnection();

                //add & return the ID 
                comm.CommandText = "INSERT INTO ExcelFile (updated_on,email) Values (@updated_on,@email);" + "Select Scope_Identity()";
                comm.Parameters.AddWithValue("@updated_on", DateTime.UtcNow.AddHours(8));
                comm.Parameters.AddWithValue("@email", email);
                fileID = Convert.ToInt32(comm.ExecuteScalar());

                foreach (Student student in studentList)
                {

                    if (!checkExist(student))
                    {
                        //check if the student type= graduated
                        if (student.Student_type.ToLower() == "graduated")
                        {
                            comm = SqlCommandConnection();
                            comm.CommandText = "INSERT INTO Student (name,nric,passport_no,gender,dob,diploma,poly_school,diploma_graduated_year,uni_name,matric_year,uni_faculty,uni_degree,uni_graduated_year,class_of_honors,scholarship,awards_dean_list,student_type,file_id) Values (@name,@nric,@passport_no,@gender,@dob,@diploma,@poly_school,@diploma_graduated_year,@uni_name,@matric_year,@uni_faculty,@uni_degree,@uni_graduated_year,@class_of_honors,@scholarship,@awards_dean_list,@student_type,@file_id);" + "Select Scope_Identity()";
                            comm.Parameters.AddWithValue("@name", student.Name);
                            comm.Parameters.AddWithValue("@nric", student.NRIC1);
                            comm.Parameters.AddWithValue("@passport_no", student.Passport_no);
                            comm.Parameters.AddWithValue("@gender", student.Gender);
                            comm.Parameters.AddWithValue("@dob", student.Dob);
                            comm.Parameters.AddWithValue("@diploma", student.Diploma);
                            comm.Parameters.AddWithValue("@uni_name", student.Uni_name);
                            comm.Parameters.AddWithValue("@poly_school", student.Poly_school);
                            comm.Parameters.AddWithValue("@diploma_graduated_year", student.Diploma_graduated_year);
                            comm.Parameters.AddWithValue("@matric_year", student.Matric_year);
                            comm.Parameters.AddWithValue("@uni_faculty", student.Uni_faculty);
                            comm.Parameters.AddWithValue("@uni_degree", student.Uni_degree);
                            comm.Parameters.AddWithValue("@uni_graduated_year", student.Uni_graduated_year);
                            comm.Parameters.AddWithValue("@class_of_honors", student.Class_of_honours);
                            comm.Parameters.AddWithValue("@scholarship", student.Scholarship);
                            comm.Parameters.AddWithValue("@awards_dean_list", student.Awards_dean_list);
                            comm.Parameters.AddWithValue("@student_type", "graduated");
                            comm.Parameters.AddWithValue("@file_id", fileID);

                        }
                        // if student type= matriculated
                        else
                        {
                            comm = SqlCommandConnection();
                            comm.CommandText = "INSERT INTO Student (name,nric,passport_no,gender,dob,diploma,poly_school,diploma_graduated_year,uni_name,uni_application_year,uni_faculty,uni_degree,ddp_cdp_course,scholarship,student_type,file_id) Values (@name,@nric,@passport_no,@gender,@dob,@diploma,@poly_school,@diploma_graduated_year,@uni_name,@uni_application_year,@uni_faculty,@uni_degree,@ddp_cdp_course,@scholarship,@student_type,@file_id);" + "Select Scope_Identity()";
                            comm.Parameters.AddWithValue("@name", student.Name);
                            comm.Parameters.AddWithValue("@nric", student.NRIC1);
                            comm.Parameters.AddWithValue("@passport_no", student.Passport_no);
                            comm.Parameters.AddWithValue("@gender", student.Gender);
                            comm.Parameters.AddWithValue("@dob", student.Dob);
                            comm.Parameters.AddWithValue("@diploma", student.Diploma);
                            comm.Parameters.AddWithValue("@uni_name", student.Uni_name);
                            comm.Parameters.AddWithValue("@poly_school", student.Poly_school);
                            comm.Parameters.AddWithValue("@diploma_graduated_year", student.Diploma_graduated_year);
                            comm.Parameters.AddWithValue("@uni_application_year", student.Uni_application_year);
                            comm.Parameters.AddWithValue("@uni_faculty", student.Uni_faculty);
                            comm.Parameters.AddWithValue("@uni_degree", student.Uni_degree);
                            comm.Parameters.AddWithValue("@ddp_cdp_course", student.Ddp_cdp_course);
                            comm.Parameters.AddWithValue("@scholarship", student.Scholarship);
                            comm.Parameters.AddWithValue("@student_type", "matriculated");
                            comm.Parameters.AddWithValue("@file_id", fileID);
                        }
                    }

                    student.Student_id = comm.ExecuteScalar().ToString();
                    student.File_id = fileID.ToString();
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                SqlCloseConnection();
            }

            return studentList;// == 0 ? false : true;
        }

        public List<Student> updateAndDeleteStudent1(List<Student> studentList)
        {
            List<Student> updateAndDeleteFailList = new List<Student>();

            foreach (Student student in studentList)
            {
                if (student.ChkDelete == true)
                {
                    deleteStudent(student);
                }
                else
                {
                    updateStudent(student);
                }
            }

            return updateAndDeleteFailList;
        }

        public bool deleteStudent(Student student)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "DELETE FROM Student WHERE student_id = @uid";
            comm.Parameters.AddWithValue("@uid", student.Student_id);
            int count = comm.ExecuteNonQuery();
            SqlCloseConnection();
            return count == 0 ? false : true;
        }

        public bool updateStudent(Student student)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            if (student.Student_type == "graduated")
            {
                comm.CommandText = "Update Student set name=@name, nric=@nric,passport_no=@passport_no,gender=@gender,dob=@dob,diploma=@diploma,uni_name=@uni_name,poly_school=@poly_school,diploma_graduated_year=@diploma_graduated_year,matric_year=@matric_year,uni_faculty=@uni_faculty,uni_degree=@uni_degree, uni_graduated_year=@uni_graduated_year,class_of_honors=@class_of_honors,scholarship=@scholarship,awards_dean_list=@awards_dean_list,student_type=@student_type, file_id=@file_id where student_id = @uid;";
                comm.Parameters.AddWithValue("@uid", student.Student_id);
                comm.Parameters.AddWithValue("@name", student.Name);
                comm.Parameters.AddWithValue("@nric", student.NRIC1);
                comm.Parameters.AddWithValue("@passport_no", student.Passport_no);
                comm.Parameters.AddWithValue("@gender", student.Gender);
                comm.Parameters.AddWithValue("@dob", student.Dob);
                comm.Parameters.AddWithValue("@diploma", student.Diploma);
                comm.Parameters.AddWithValue("@uni_name", student.Uni_name);
                comm.Parameters.AddWithValue("@poly_school", student.Poly_school);
                comm.Parameters.AddWithValue("@diploma_graduated_year", student.Diploma_graduated_year);
                comm.Parameters.AddWithValue("@matric_year", student.Matric_year);
                comm.Parameters.AddWithValue("@uni_faculty", student.Uni_faculty);
                comm.Parameters.AddWithValue("@uni_degree", student.Uni_degree);
                comm.Parameters.AddWithValue("@uni_graduated_year", student.Uni_graduated_year);
                comm.Parameters.AddWithValue("@class_of_honors", student.Class_of_honours);
                comm.Parameters.AddWithValue("@scholarship", student.Scholarship);
                comm.Parameters.AddWithValue("@awards_dean_list", student.Awards_dean_list);
                comm.Parameters.AddWithValue("@student_type", "graduated");
                comm.Parameters.AddWithValue("@file_id", student.File_id);
            }
            else
            {
                comm.CommandText = "Update Student set name=@name, nric=@nric,passport_no=@passport_no,gender=@gender,dob=@dob,diploma=@diploma,uni_name=@uni_name,poly_school=@poly_school,diploma_graduated_year=@diploma_graduated_year,uni_application_year=@uni_application_year, uni_faculty=@uni_faculty, uni_degree=@uni_degree, ddp_cdp_course=@ddp_cdp_course,scholarship=@scholarship,student_type=@student_type,file_id = @file_id where student_id = @uid;";
                comm.Parameters.AddWithValue("@uid", student.Student_id);
                comm.Parameters.AddWithValue("@name", student.Name);
                comm.Parameters.AddWithValue("@nric", student.NRIC1);
                comm.Parameters.AddWithValue("@passport_no", student.Passport_no);
                comm.Parameters.AddWithValue("@gender", student.Gender);
                comm.Parameters.AddWithValue("@dob", student.Dob);
                comm.Parameters.AddWithValue("@diploma", student.Diploma);
                comm.Parameters.AddWithValue("@uni_name", student.Uni_name);
                comm.Parameters.AddWithValue("@poly_school", student.Poly_school);
                comm.Parameters.AddWithValue("@diploma_graduated_year", student.Diploma_graduated_year);
                comm.Parameters.AddWithValue("@uni_application_year", student.Uni_application_year);
                comm.Parameters.AddWithValue("@uni_faculty", student.Uni_faculty);
                comm.Parameters.AddWithValue("@uni_degree", student.Uni_degree);
                comm.Parameters.AddWithValue("@ddp_cdp_course", student.Ddp_cdp_course);
                comm.Parameters.AddWithValue("@scholarship", student.Scholarship);
                comm.Parameters.AddWithValue("@student_type", "matriculated");
                comm.Parameters.AddWithValue("@file_id", student.File_id);
            }

            int result = comm.ExecuteNonQuery();
            SqlCloseConnection();

            if (result == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
            //return result == 0 ? false : true;
        }

        public bool checkExist(Student student)
        {
            int temp = 0;
            try
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "Select student_id From Student where nric=@nric,name=@name,uni_name=@uni_name,student_type=@student_type,passport_no=@passport_no,gender=@gender,dob=@dob,poly_school=@poly_school,diploma=@diploma,diploma_graduated_year=@diploma_graduated_year,matric_year=@matric_year,uni_application_year=@uni_application_year,uni_faculty=@uni_faculty,uni_degree=@uni_degree,uni_graduated_year=@uni_graduated_year,class_of_honors=@class_of_honors,scholarship=@scholarship,awards_dean_list=@awards_dean_list,ddp_cdp_course=@ddp_cdp_course;";
                comm.Parameters.AddWithValue("@name", student.Name);
                comm.Parameters.AddWithValue("@nric", student.NRIC1);
                comm.Parameters.AddWithValue("@passport_no", student.Passport_no);
                comm.Parameters.AddWithValue("@gender", student.Gender);
                comm.Parameters.AddWithValue("@dob", student.Dob);
                comm.Parameters.AddWithValue("@diploma", student.Diploma);
                comm.Parameters.AddWithValue("@uni_name", student.Uni_name);
                comm.Parameters.AddWithValue("@poly_school", student.Poly_school);
                comm.Parameters.AddWithValue("@diploma_graduated_year", student.Diploma_graduated_year);
                comm.Parameters.AddWithValue("@uni_application_year", student.Uni_application_year);
                comm.Parameters.AddWithValue("@uni_faculty", student.Uni_faculty);
                comm.Parameters.AddWithValue("@uni_degree", student.Uni_degree);
                comm.Parameters.AddWithValue("@ddp_cdp_course", student.Ddp_cdp_course);
                comm.Parameters.AddWithValue("@scholarship", student.Scholarship);
                comm.Parameters.AddWithValue("@matric_year", student.Matric_year);
                comm.Parameters.AddWithValue("@uni_graduated_year", student.Uni_graduated_year);
                comm.Parameters.AddWithValue("@class_of_honors", student.Class_of_honours);
                comm.Parameters.AddWithValue("@awards_dean_list", student.Awards_dean_list);
                comm.Parameters.AddWithValue("@student_type", student.Student_type);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    temp = Convert.ToInt16(dr["student_id"].ToString());
                }
            }
            catch (Exception)
            {
            }
            finally
            {
            }


            if (temp == 0)
            {
                return false;
            }
            else
            {
                return true;
            }


            // return temp == 0 ? false : true;
        }

        public List<Student> getStudentByFileID(string fileID)
        {
            List<Student> studentList = new List<Student>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select * FROM Student WHERE file_id = @file_id;";
            comm.Parameters.AddWithValue("@file_id", fileID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                Student temp = new Student();
                temp.Name = dr["name"].ToString();
                temp.Student_id = dr["student_id"].ToString();
                temp.NRIC1 = dr["nric"].ToString();
                temp.Uni_name = dr["uni_name"].ToString();
                temp.Student_type = dr["student_type"].ToString();
                temp.Passport_no = dr["passport_no"].ToString();
                temp.Gender = dr["gender"].ToString();
                temp.Dob = Convert.ToDateTime(dr["dob"]);
                temp.Poly_school = dr["poly_school"].ToString();
                temp.Diploma = dr["diploma"].ToString();
                temp.Diploma_graduated_year = dr["diploma_graduated_year"].ToString();
                temp.Matric_year = dr["matric_year"].ToString();
                temp.Uni_application_year = dr["uni_application_year"].ToString();
                temp.Uni_faculty = dr["uni_faculty"].ToString();
                temp.Uni_degree = dr["uni_degree"].ToString();
                temp.Uni_graduated_year = dr["uni_graduated_year"].ToString();
                temp.Class_of_honours = dr["class_of_honors"].ToString();
                temp.Scholarship = dr["scholarship"].ToString();
                temp.Awards_dean_list = dr["awards_dean_list"].ToString();
                temp.Ddp_cdp_course = dr["ddp_cdp_course"].ToString();
                temp.File_id = dr["file_id"].ToString();


                studentList.Add(temp);
            }

            return studentList;
        }

        public List<Student> searchStudent(string searchCriteria, string searchSpecify)
        {
            List<Student> studentList = new List<Student>();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();

            if (searchCriteria == "Name")
            {
                comm.CommandText = "Select * FROM Student WHERE name like @search;";
            }

            else if (searchCriteria == "NRIC")
            {
                comm.CommandText = "Select * FROM Student WHERE nric like @search;";
            }

            else if (searchCriteria == "Gender")
            {
                comm.CommandText = "Select * FROM Student WHERE gender like @search;";
            }

            else if (searchCriteria == "DOB")
            {
                comm.CommandText = "Select * FROM Student WHERE dob like @search;";
            }

            else if (searchCriteria == "Passport Number")
            {
                comm.CommandText = "Select * FROM Student WHERE passport_no like @search;";
            }

            else if (searchCriteria == "University Name")
            {
                comm.CommandText = "Select * FROM Student WHERE uni_name like @search;";
            }

            else if (searchCriteria == "Studet Type")
            {
                comm.CommandText = "Select * FROM Student WHERE student_type like @search;";
            }

            else if (searchCriteria == "TP School Name")
            {
                comm.CommandText = "Select * FROM Student WHERE poly_school like @search;";
            }

            else if (searchCriteria == "TP Diploma")
            {
                comm.CommandText = "Select * FROM Student WHERE diploma like @search;";
            }

            else if (searchCriteria == "TP Diploma Graduated Year")
            {
                comm.CommandText = "Select * FROM Student WHERE diploma_graduated_year like @search;";
            }


            else if (searchCriteria == "University Applicatio Year")
            {
                comm.CommandText = "Select * FROM Student WHERE uni_application_year like @search;";
            }


            else if (searchCriteria == "University Matriculated Year")
            {
                comm.CommandText = "Select * FROM Student WHERE matric_year like @search;";
            }


            else if (searchCriteria == "University Faculty")
            {
                comm.CommandText = "Select * FROM Student WHERE uni_faculty like @search;";
            }


            else if (searchCriteria == "University Degree Name")
            {
                comm.CommandText = "Select * FROM Student WHERE uni_degree like @search;";
            }

            else if (searchCriteria == "University Graduated Year")
            {
                comm.CommandText = "Select * FROM Student WHERE uni_graduated_year like @search;";
            }

            else if (searchCriteria == "Class of Honors")
            {
                comm.CommandText = "Select * FROM Student WHERE class_of_honors like @search;";
            }

            else if (searchCriteria == "Scholarship")
            {
                comm.CommandText = "Select * FROM Student WHERE scholarship like @search;";
            }

            else if (searchCriteria == "Prize / Award / Deans")
            {
                comm.CommandText = "Select * FROM Student WHERE awards_dean_list like @search;";
            }

            else if (searchCriteria == "DDP / CDP Course")
            {
                comm.CommandText = "Select * FROM Student WHERE ddp_cdp_course like @search;";
            }

            comm.Parameters.AddWithValue("@search", searchSpecify + "%");
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                Student temp = new Student();
                temp.Student_id = dr["student_id"].ToString();
                temp.NRIC1 = dr["nric"].ToString();
                temp.Name = dr["name"].ToString();
                temp.Uni_name = dr["uni_name"].ToString();
                temp.Student_type = dr["student_type"].ToString();
                temp.Passport_no = dr["passport_no"].ToString();
                temp.Gender = dr["gender"].ToString();
                temp.Dob = Convert.ToDateTime(dr["dob"]);
                temp.Poly_school = dr["poly_school"].ToString();
                temp.Diploma = dr["diploma"].ToString();
                temp.Diploma_graduated_year = dr["diploma_graduated_year"].ToString();
                temp.Matric_year = dr["matric_year"].ToString();
                temp.Uni_application_year = dr["uni_application_year"].ToString();
                temp.Uni_faculty = dr["uni_faculty"].ToString();
                temp.Uni_degree = dr["uni_degree"].ToString();
                temp.Uni_graduated_year = dr["uni_graduated_year"].ToString();
                temp.Class_of_honours = dr["class_of_honors"].ToString();
                temp.Scholarship = dr["scholarship"].ToString();
                temp.Awards_dean_list = dr["awards_dean_list"].ToString();
                temp.Ddp_cdp_course = dr["ddp_cdp_course"].ToString();
                temp.File_id = dr["file_id"].ToString();


                studentList.Add(temp);
            }

            return studentList;
        }
        #endregion


        #region  CRUD for User

        public int addNewUser(string email, string name, string user_type, string password)
        {
            int id = 0;
            try
            {
                SqlOpenConnection();
                SqlCommand comm;


                comm = SqlCommandConnection();
                comm.CommandText = "INSERT INTO Userx (email, name, pwd, status, user_type) Values (@email, @name, @password, @status, @user_type)";
                comm.Parameters.AddWithValue("@email", email);
                comm.Parameters.AddWithValue("@name", name);
                comm.Parameters.AddWithValue("@user_type", user_type);
                comm.Parameters.AddWithValue("@password", password);
                comm.Parameters.AddWithValue("@status", "pending");

                id = Convert.ToInt32(comm.ExecuteNonQuery());

            }
            catch (Exception)
            {
            }
            finally
            {
                SqlCloseConnection();
            }

            return id;// == 0 ? false : true;
        }

        public bool updateUser(User user)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            
                comm.CommandText = "Update Userx set pwd=@password, name=@name, status=@status, user_type=@user_type  where email = @email;";
                comm.Parameters.AddWithValue("@email", user.Email);
                comm.Parameters.AddWithValue("@password", user.Password);
                comm.Parameters.AddWithValue("@name", user.Name);
                comm.Parameters.AddWithValue("@status", user.Status);
                comm.Parameters.AddWithValue("@user_type", user.User_type);
                

            int result = comm.ExecuteNonQuery();
            SqlCloseConnection();

            if (result == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
            //return result == 0 ? false : true;
        }

        public bool deleteUser(User user)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "DELETE FROM Userx WHERE email = @email";
            comm.Parameters.AddWithValue("@email", user.Email);
            int count = comm.ExecuteNonQuery();
            SqlCloseConnection();
            return count == 0 ? false : true;
        }

        public List<User> updateAndDeleteUser(List<User> userList)
        {
            List<User> updateAndDeleteFailList = new List<User>();

            foreach (User user in userList)
            {
                if (user.ChkDelete == true)
                {
                    deleteUser(user);
                }
                else
                {
                    updateUser(user);
                }
            }

            return updateAndDeleteFailList;
        }

        public List<User> getAllUser()
        {
            List<User> userList = new List<User>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select * FROM Userx;";

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                User temp = new User();
                temp.Email = dr["email"].ToString();
                temp.Password = dr["pwd"].ToString();
                temp.Name = dr["name"].ToString();
                temp.Status = dr["status"].ToString();
                temp.User_type = dr["user_type"].ToString();

                userList.Add(temp);
            }

            return userList;
        }

        public List<User> getPendingUser()
        {
            List<User> userList = new List<User>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select * FROM Userx WHERE status='pending'";

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                User temp = new User();
                temp.Email = dr["email"].ToString();
                temp.Password = dr["pwd"].ToString();
                temp.Name = dr["name"].ToString();
                temp.Status = dr["status"].ToString();
                temp.User_type = dr["user_type"].ToString();

                userList.Add(temp);
            }

            return userList;
        }


        public List<User> approveUser(List<User> userList)
        {
            List<User> approveUserList = new List<User>();

            foreach (User user in userList)
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();

                comm.CommandText = "Update Userx set status=@status where email = @email;";
                comm.Parameters.AddWithValue("@email", user.Email);
                comm.Parameters.AddWithValue("@status", "Approved");

                int result = comm.ExecuteNonQuery();
                SqlCloseConnection();
            }

            return approveUserList;
        }

        #endregion


        #region joel's unfinished methods

        //public int changePw(string email, string password)
        //{
        //    int id = 0;
        //    try
        //    {
        //        SqlOpenConnection();
        //        SqlCommand comm;


        //        comm = SqlCommandConnection();
        //        comm.CommandText = "UPDATE Userx SET pwd = @password WHERE email = @email";
        //        comm.Parameters.AddWithValue("@email", email);
        //        comm.Parameters.AddWithValue("@password", password);

        //        id = Convert.ToInt32(comm.ExecuteNonQuery());

        //    }
        //    catch (Exception)
        //    {
        //    }
        //    finally
        //    {
        //        SqlCloseConnection();
        //    }

        //    return id;// == 0 ? false : true;
        //}


        public bool SendEmail(string fromEmail, string toEmail, string subject, string body)
        {
            try
            {

                MailMessage message = new MailMessage();
                message.From = new MailAddress(fromEmail);
                message.To.Add(new MailAddress(toEmail));
                message.Subject = subject;
                message.Body = body;
                message.IsBodyHtml = true;

                SmtpClient smtp = new SmtpClient();
                smtp.EnableSsl = true;
                smtp.Send(message);

                return true;
            }
            catch
            {
                return false;
            }

        }


        //public int login(string email, string password)
        //{
        //    int id = 0;
        //    try
        //    {
        //        SqlOpenConnection();
        //        SqlCommand comm;

        //        comm = SqlCommandConnection();
        //        comm.CommandText = "SELECT * FROM userx WHERE email = @email AND pwd = @password";

        //        comm.Parameters.AddWithValue("@email", email);
        //        comm.Parameters.AddWithValue("@password", password);

        //        id = Convert.ToInt32(comm.ExecuteNonQuery());

        //    }
        //    catch (Exception)
        //    {
        //    }
        //    finally
        //    {
        //        SqlCloseConnection();
        //    }

        //    return id;// == 0 ? false : true;
        //}


        //random sample code below
        //public void ReadData()
        //{
        //    SqlDataReader rdr = null;

        //    try
        //    {
        //        SqlOpenConnection();
        //        SqlCommand comm;

        //        comm = SqlCommandConnection();
        //        comm.CommandText = "SELECT * FROM userx WHERE email = @email AND pwd = @password";

        //        // 2. Call Execute reader to get query results
        //        rdr = comm.ExecuteReader();

        //        // print the CategoryName of each record
        //        while (rdr.Read())
        //        {
        //            Console.WriteLine(rdr[0]);
        //        }
        //    }
        //    finally
        //    {
        //        // close the reader
        //        if (rdr != null)
        //        {
        //            rdr.Close();
        //        }

        //        // Close the connection
        //        if (conn != null)
        //        {
        //            conn.Close();
        //        }
        //    }
        //}





        #endregion

    }
}
